#!/bin/bash
# 定义变量：数据同步日期，表示同步哪一条日志数据
# 第1、执行shell脚本时，传递参数
# 第2、如果没有传递参数，同步前一天数据
if [ -n "$1" ] ; then
data_date=$1
else
data_date=`date -d "-1 days" +%F`
fi
# 加载数据语句
DIM_MD_AREAS_SQL="
DROP temporary FUNCTION IF EXISTS jwtest ;
DROP temporary FUNCTION IF EXISTS jwtest1 ;
add jar hdfs://node101:8020/warehouse/UDF-1.0-SNAPSHOT.jar;
create temporary function jwtest as 'com.bw.UDF.MyUDF';
WITH
    tmp_area AS (
        SELECT
            ID, AREANAME, PARENTID, BD09_LAT, BD09_LNG
        FROM jtp_app_ods.ods_md_areas
        WHERE dt = '${data_date}'
    )
   , tmp1 AS (
    SELECT ID, AREANAME AS province_name
    FROM tmp_area
    WHERE PARENTID = 0
)
   , tmp2 AS (
    SELECT
        tmp_area.ID
         , tmp1.province_name
         , tmp_area.AREANAME AS city_name
    FROM tmp1
             LEFT JOIN tmp_area ON tmp1.ID = tmp_area.PARENTID
)
   , tmp3 AS (
    SELECT
        tmp_area.ID
         , tmp2.province_name
         , tmp2.city_name
         , tmp_area.AREANAME AS district_name
    FROM tmp2
             LEFT JOIN tmp_area ON tmp2.ID = tmp_area.PARENTID
)
INSERT OVERWRITE TABLE jtp_app_dim.dim_md_areas PARTITION (dt = '${data_date}')
SELECT
    tmp_area.ID
     , tmp3.province_name
     , tmp3.city_name
     , tmp3.district_name
     , tmp_area.AREANAME AS street_name
     , tmp_area.BD09_LAT
     , tmp_area.BD09_LNG
     , jwtest(BD09_LNG, BD09_LAT, 6) AS geo_hash
FROM tmp3
         LEFT JOIN tmp_area ON tmp3.ID = tmp_area.PARENTID
WHERE tmp_area.BD09_LNG IS NOT NULL AND tmp_area.BD09_LAT IS NOT NULL
;
"
# 执行SQL语句
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${DIM_MD_AREAS_SQL}"